package com.crm.dao.client;

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.crm.pojo.Memorial;
import com.crm.vo.MemorialVo;

public interface MemorialDao extends CrudRepository<Memorial, Long>,JpaSpecificationExecutor<Memorial> {
	/**
	 * 查询所有的纪念日
	 * @param pageable
	 * @return
	 */
	@Query("select new com.crm.vo.MemorialVo(m, c,"
			+ "add_months(m.memDate,ceil(months_between(sysdate,m.memDate )/12)*12) as nextMem) "
			+ "from Memorial m,Contact c where m.clientId =c.contactid and m.memorialIsDel != '是' order by nextMem ")
	public List<MemorialVo> queryAllMemorial(Pageable pageable);
	
	/**
	 * 查询所有的纪念日的数量
	 * @return
	 */
	@Query("select count(*) "
			+ "from Memorial m,Contact c where m.clientId =c.contactid and m.memorialIsDel != '是'")
	public Long queryCountAllMemorial();
	
	/**
	 * 根据id查询纪念日
	 * @param memorialVo
	 * @return
	 */
	@Query("select new com.crm.vo.MemorialVo(m, c,"
			+ "add_months(m.memDate,ceil(months_between(sysdate,m.memDate )/12)*12) as nextMem) "
			+ "from Memorial m,Contact c where m.clientId =c.contactid and m.memorialIsDel != '是' and m.memId = ?1  order by nextMem ")
	public List<MemorialVo> queryMemorialByMemId(Long memId, Pageable pageable);
	
	/**
	 * 根据id查询所有的纪念日的数量
	 * @param memorialVo
	 * @return
	 */
	@Query("select count(*) "
			+ "from Memorial m,Contact c where m.clientId =c.contactid and m.memorialIsDel != '是' and m.memId = ?1")
	public Long queryCountMemorialByMemId(Long memId);
	
	/**
	 * 根据联系人姓名模糊查询纪念日
	 * @param memorialVo
	 * @return
	 */
	@Query("select new com.crm.vo.MemorialVo(m, c,"
			+ "add_months(m.memDate,ceil(months_between(sysdate,m.memDate )/12)*12) as nextMem) "
			+ "from Memorial m,Contact c where m.clientId =c.contactid and m.memorialIsDel != '是' and c.contactName like ?1  order by nextMem ")
	public List<MemorialVo> queryMemorialByContactName(String contactName, Pageable pageable);
	
	/**
	 * 根据联系人姓名模糊查询所有的纪念日的数量
	 * @param memorialVo
	 * @return
	 */
	@Query("select count(*) "
			+ "from Memorial m,Contact c where m.clientId =c.contactid and m.memorialIsDel != '是' and c.contactName like ?1")
	public Long queryCountMemorialByContactName(String contactName);
	
	/**
	 * 查询当月的纪念日
	 * @param pageable
	 * @return
	 */
	@Query("select new com.crm.vo.MemorialVo(m, c,"
			+ "add_months(m.memDate,ceil(months_between(sysdate,m.memDate )/12)*12) as nextMem) "
			+ "from Memorial m,Contact c where m.clientId =c.contactid and m.memorialIsDel != '是' "
			+ "and  Extract(MONTH from add_months(m.memDate,ceil(months_between(sysdate,m.memDate )/12)*12)) = Extract(MONTH from sysdate) \r\n" + 
			" and Extract(YEAR from add_months(m.memDate,ceil(months_between(sysdate,m.memDate )/12)*12)) = Extract(YEAR from sysdate)")
	public List<MemorialVo> queryMemorialCurMonth();
	
	/**
	 * 查询下个月的纪念日
	 * @param pageable
	 * @return
	 */
	@Query("select new com.crm.vo.MemorialVo(m, c,"
			+ "add_months(m.memDate,ceil(months_between(sysdate,m.memDate )/12)*12) as nextMem) "
			+ "from Memorial m,Contact c where m.clientId =c.contactid and m.memorialIsDel != '是' "
			+ "and  Extract(MONTH from add_months(m.memDate,ceil(months_between(sysdate,m.memDate )/12)*12)) = Extract(MONTH from sysdate) + 1 \r\n" + 
			" and Extract(YEAR from add_months(m.memDate,ceil(months_between(sysdate,m.memDate )/12)*12)) = Extract(YEAR from sysdate)")
	public List<MemorialVo> queryMemorialNextMonth();
	
	
}
